#!/usr/bin/env python
# -*- coding: utf-8 -*-
"""
>>> db =connect('sqlite://:memory:')
>>> db
<tack.db.adapter.SqliteAdapter object at 0x...>

>>> db._do_query('CREATE TABLE tt '
... '(calendar_id INTEGER PRIMARY KEY AUTOINCREMENT, user_id integer, '
... 'title character varying(128), summary character varying(4096), '
... 'clocation character varying(128), color character varying(7), '
... 'timezone character varying(64), hidden integer DEFAULT 0)', {})

>>> db.insert('tt', user_id=1, title='A Title', summary='A Summary',
... clocation='A Location', color='#FFFFFF', timezone='American/Anchorage',
... hidden=1)
1

>>> db.select('select * from tt')
[(1, 1, u'A Title', u'A Summary', u'A Location', u'#FFFFFF', u'American/Anchorage', 1)]

>>> db.insert('tt', user_id=1, title='Another Title', summary='A Summary',
... clocation='A Location', color='#FFFFFF', timezone='American/Anchorage',
... hidden=1)
2

>>> len(db.select('select * from tt'))
2

>>> db.qupdate('tt', 'calendar_id', calendar_id=1, title='Testing Title')
1

>>> rows = db.select('select * from tt ORDER BY calendar_id ASC')
>>> rows[0][2]
u'Testing Title'

>>> db.row_dict(rows[0], clocation='where')
{'user_id': 1, 'title': u'Testing Title', 'color': u'#FFFFFF', 'summary': u'A Summary', 'timezone': u'American/Anchorage', 'calendar_id': 1, 'hidden': 1, 'where': u'A Location'}

>>> db.last_select_len = -1
>>> db.row_dict(rows[0], clocation='where')
Traceback (most recent call last):
...
ProgrammingError: Row length does not match last select

>>> db.update("UPDATE tt SET title = $title WHERE calendar_id = $calendar_id",
... title='A new title', calendar_id = 2)
1

>>> rows = db.select('select * from tt ORDER BY calendar_id ASC')
>>> rows[1][2]
u'A new title'

>>> db.qdelete('tt', calendar_id=1)
1

>>> rows = db.select('select * from tt ORDER BY calendar_id ASC')
>>> rows[0][2]
u'A new title'

>>> db.delete('DELETE FROM tt WHERE calendar_id = $calendar_id', calendar_id=2)
1

>>> db.select('select * from tt')
[]

>>> db.paramstyle = 'qmark'
>>> db._sub_params('$this, $that', dict(this=1, that=2))
('?, ?', [1, 2])

>>> db.paramstyle = 'numeric'
>>> db._sub_params('$this, $that', dict(this=1, that=2))
(':1, :2', [1, 2])

>>> db.paramstyle = 'format'
>>> db._sub_params('$this, $that', dict(this=1, that=2))
('%s, %s', [1, 2])

>>> db.paramstyle = 'named'
>>> db._sub_params('$this, $that', dict(this=1, that=2))
(':this, :that', [])

>>> db.paramstyle = 'pyformat'
>>> db._sub_params('$this, $that', dict(this=1, that=2))
('%(this)s, %(that)s', [])

"""
import os
import re

from datetime import datetime
from functools import partial
from re import compile as recompile
from string import Template
from time import strptime

from tack.lib.jsontools import json_dumps

def connect(dsn):
    ## support for legacy pyodbc and sqlite3 DSN values:
    if dsn.startswith('Driver={SQL Server}'):
        klass = PyodbcAdapter
    elif dsn == ':memory:':
        klass = SqliteAdapter

    ## support for new DSN values where the database driver given expclictly:
    elif dsn.startswith('pyodbc://'):
        klass = PyodbcAdapter
        dsn = dsn[len('pyodbc://'):]
    elif dsn.startswith('sqlite://'):
        klass = SqliteAdapter
        dsn = dsn[len('sqlite://'):]
    elif dsn.startswith('pymssql://'):
        klass = PyMsSqlAdapter
        dsn = dsn[len('pymssql://'):]
    elif dsn.startswith('postgresql://'):
        klass = PostgresqlAdapter
        dsn = dsn[len('postgresql://'):]
    try:
        return klass(dsn)
    except (NameError, ), nexc:
        raise TypeError("No suitable database adapter found.")


class DatabaseAdapter(object):
    unsafe_strings = (
        ("'", "''"),
        ("\\", "\\\\"),
        (";", "\;"),
    )

    ## bool in (0, 1) bool out (False, True)

    def __init__(self, dsn):
        self.dsn = dsn
        self.connection = self.libconnect(dsn)
        self.cursor = self.connection.cursor()
        self.param_re = recompile('\$([a-zA-Z0-9_]*)')
        self.last_select_len = 0

    def close(self):
        self.connection.close()

    def describe(self, relation):
        self.cursor.execute('SELECT * FROM "%s" WHERE 1=0' % relation)
        return self.cursor.description

    def _sub_params(self, sql, params):
        """
        Takes an SQL string with parameters indicated by $name, replaces
        $name with the driver's DB API paramstyle, letting the driver do the
        proper escaping.
        """
        param_list = []
        pla = param_list.append

        for num, param_name in enumerate(self.param_re.findall(sql)):
            if self.paramstyle == 'qmark':
                placeholder = '?'
                pla(params[param_name])
            elif self.paramstyle == 'numeric':
                placeholder = ':' + str(num + 1)
                pla(params[param_name])
            elif self.paramstyle == 'format':
                placeholder = '%s'
                pla(params[param_name])
            elif self.paramstyle == 'named':
                placeholder = ':' + param_name
            elif self.paramstyle == 'pyformat':
                placeholder = '%%(%s)s' % param_name

            sql = sql.replace('$' + param_name, placeholder, 1)

        return sql, param_list

    def _do_query(self, sql, params):
        sql, param_list = self._sub_params(sql, params)
        if param_list:
            self.cursor.execute(sql, param_list)
        else:
            self.cursor.execute(sql, params)

    def select(self, sql, **params):
        self._do_query(sql, params)
        self.last_select_len = len(self.cursor.description)
        return self.cursor.fetchall()

    def insert(self, relation, **params):
        columns = []
        values = []
        for k in params.keys():
            columns.append(k)
            values.append('$' + k)
        sql = ('INSERT INTO %s (%s) VALUES (%s)' %
               (relation, ', '.join(columns), ', '.join(values)))
        self._do_query(sql, params)
        self.connection.commit()
        row_id = self.lastrowid
        return row_id

    def _modify(self, sql, params):
        self._do_query(sql, params)
        self.connection.commit()
        return self.cursor.rowcount

    # return int rows affected
    def delete(self, sql, **params):
        return self._modify(sql, params)

    def qdelete(self, relation, **params):
        sql = 'DELETE FROM %s WHERE ' % relation
        sql = sql + ' AND '.join(["%s=$%s" % (k,k) for k in params.keys()])
        return self._modify(sql, params)

    # return int rows affected
    def update(self, sql, **params):
        return self._modify(sql, params)

    def qupdate(self, relation, key_name, **params):
        """
        Assumes the update has only one field in the WHERE clause: the
        artificial key.
        Requires the key_name/key_value to to be a key/value pair in params
        Exclude key from SET clause, so cannot be used to (accidentally) change
        the record's key.
        """
        sql = 'UPDATE %s SET ' % relation
        sql += ', '.join('%s=$%s' % (k,k) for k in params.keys()
                         if k != key_name)
        sql += ' WHERE %s=$%s' % (key_name, key_name)
        return self._modify(sql, params)

    # NOTE: These next three functions will only behave as expected if called
    # on rows retrieved from the most recent SELECT.  Rows are "dumb" tuples,
    # so do not carry their descriptions with them.  Thus, the most recent
    # cursor.description must be used.
    # The check on the length of the row might catch some of this, but if
    # two different selects have the same row length, nothing will stop the
    # columns from being mis-labeled.
    # key_subst is a dict of 'old_name':'new_name' pairs for renaming fields
    # when converting rows to dicts
    def row_dict(self, row, **key_subst):
        if len(row) != self.last_select_len:
            raise self.ProgrammingError('Row length does not match last select')

        def col_name(n):
            if n in key_subst:
                return key_subst[n]
            else:
                return n

        return dict((col_name(c[0]), row[i]) for i, c
                    in enumerate(self.cursor.description))

    def row_dumps(self, row):
        return json_dumps(self.row_dict(row), indent=2)

    def rows_dumps(self, rows):
        return json_dumps([self.row_dict(row) for row in rows], indent=2)

class SqliteAdapter(DatabaseAdapter):
    """
    Features:
      - supports qmark and named paramstyle
      - supports rowcount for rows modified
      - supports lastrowid
    """
    def __init__(self, dsn):
        self.paramstyle = 'named'
        from sqlite3 import (Warning, Error, InterfaceError, DatabaseError,
                             DataError, OperationalError, IntegrityError,
                             InternalError, ProgrammingError, NotSupportedError)
        (self.Warning, self.Error, self.InterfaceError, self.DatabaseError,
         self.DataError, self.OperationalError, self.IntegrityError,
         self.InternalError, self.ProgrammingError, self.NotSupportedError) = (
             Warning, Error, InterfaceError, DatabaseError, DataError,
             OperationalError, IntegrityError, InternalError, ProgrammingError,
             NotSupportedError)
        DatabaseAdapter.__init__(self, dsn)

    @property
    def libconnect(self):
        from sqlite3 import connect
        return connect

    @property
    def lastrowid(self):
        return self.cursor.lastrowid

    @property
    def rowcount(self):
        return self.cursor.rowcount

class PostgresqlAdapter(DatabaseAdapter):
    """
    Features:
      - support pyformat paramstyle
      - supports lastrowid, but not sure if it returns SERIAL row or oid
      - supports 'connection' property on cursor
    """
    def __init__(self, dsn):
        self.paramstyle = 'pyformat'
        from psycopg2 import (Warning, Error, InterfaceError, DatabaseError,
                             DataError, OperationalError, IntegrityError,
                             InternalError, ProgrammingError, NotSupportedError)
        (self.Warning, self.Error, self.InterfaceError, self.DatabaseError,
         self.DataError, self.OperationalError, self.IntegrityError,
         self.InternalError, self.ProgrammingError, self.NotSupportedError) = (
             Warning, Error, InterfaceError, DatabaseError, DataError,
             OperationalError, IntegrityError, InternalError, ProgrammingError,
             NotSupportedError)
        DatabaseAdapter.__init__(self, dsn)

    @property
    def libconnect(self):
        from psycopg2 import connect
        return connect

    @property
    def lastrowid(self):
        """
        psycopg2's lastrowid returns an OID which is 1) deprecated, and
        2) always 0 if OID's are disabled on the table. Thus, what you see here
        NB: lastval() returns the last value used! Thus, if your insert triggers
        several other inserts (via triggers, functions, etc) then then lastval()
        may not be the value of the last INSERT you executed.  Something to
        keep in mind.
        See http://www.postgresql.org/docs/8.3/static/functions-sequence.html
        """
        self.cursor.execute('SELECT lastval()')
        return self.cursor.fetchall()[0][0]

    @property
    def rowcount(self):
        return self.rowcount


class PyodbcAdapter(DatabaseAdapter):
    """
    Features:
      - uses qmark paramstyle
      - rowcount is always -1, which technically is a violation of DB API 2.0
      - 'connection' is coded in the source, but is not documented
         status unknown
      - does not support lastrowid
    """
    def __init__(self, dsn):
        self.paramstyle = 'qmark'
        from pyodbc import (Warning, Error, InterfaceError, DatabaseError,
                             DataError, OperationalError, IntegrityError,
                             InternalError, ProgrammingError, NotSupportedError)
        (self.Warning, self.Error, self.InterfaceError, self.DatabaseError,
         self.DataError, self.OperationalError, self.IntegrityError,
         self.InternalError, self.ProgrammingError, self.NotSupportedError) = (
             Warning, Error, InterfaceError, DatabaseError, DataError,
             OperationalError, IntegrityError, InternalError, ProgrammingError,
             NotSupportedError)
        DatabaseAdapter.__init__(self, dsn)

    @property
    def libconnect(self):
        from pyodbc import connect
        return connect

    @property
    def lastrowid(self):
        """
        This assumes we'll be using this adapter with only MS SQL
        """
        self.cursor.execute('SELECT CAST(@@IDENTITY AS INT)')
        return self.cursor.fetchone()[0]

    @property
    def rowcount(self):
        return None


class PyMsSqlAdapter(DatabaseAdapter):
    """
    Features:
      - supports pyformat paramstyle
      - supports 'connected' property on underlying type
      - does not support lastrowid
      - does not seem to support support rowcount, even though DB API requires
    """
    def __init__(self, dsn):
        self.paramstyle = 'pyformat'
        from pymssql import (Warning, Error, InterfaceError, DatabaseError,
                             DataError, OperationalError, IntegrityError,
                             InternalError, ProgrammingError, NotSupportedError)
        (self.Warning, self.Error, self.InterfaceError, self.DatabaseError,
         self.DataError, self.OperationalError, self.IntegrityError,
         self.InternalError, self.ProgrammingError, self.NotSupportedError) = (
             Warning, Error, InterfaceError, DatabaseError, DataError,
             OperationalError, IntegrityError, InternalError, ProgrammingError,
             NotSupportedError)
        DatabaseAdapter.__init__(self, dsn)

    @property
    def libconnect(self):
        from pymssql import connect
        return partial(connect, user="", password="", host="", database="")

    @property
    def lastrowid(self):
        self.cursor.execute('SELECT CAST(@@IDENTITY AS INT)')
        return self.cursor.fetchone()[0]

    @property
    def rowcount(self):
        """
        Is there ans SQL command to pull the last number of rows modified
        from MS SQL?
        """
        return None
